package com.gm.baseplatform.core.jdbc;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class JDBCTool {
	
  private static final Log LOG = LogFactory.getLog(JDBCTool.class);

  
  public static String[][] doSQLQuery(String sql) throws SQLException {
    String sTmp="";
    String[][] sArrRtn = null;
    int iCol = 0, i = 0, j = 0;
    boolean bFlg = false;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
      JDBCWrapper jw = new JDBCWrapper();
      con = jw.getConnection();
      st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      rs = st.executeQuery(sql);
      rsmd = rs.getMetaData();
      iCol = rsmd.getColumnCount();
      bFlg = rs.last();
      if (bFlg)
        i = rs.getRow();
      rs.beforeFirst();
      sArrRtn = new String[i + 1][iCol];
      for (i = 0; i < iCol; i++) {
        sTmp = rsmd.getColumnName(i + 1).trim();
        sArrRtn[0][i] = sTmp;
      }
      while (rs.next()) {
        j = j + 1;
        for (i = 0; i < iCol; i++) {
        	 Object obj = rs.getObject(i + 1);
             if(obj!=null&&obj.getClass().getName().equalsIgnoreCase(oracle.sql.CLOB.class.getName())){
             	Clob clob =(Clob)obj;
                 if (clob != null) {
                 	sTmp = clob.getSubString(1, (int) clob.length());                          
                 }
             }
             else{
             	sTmp = String.valueOf(
             	obj == null ? "" : obj).trim();
                 if (sTmp.equals("null"))
                   sTmp = "";
             }                        
             sArrRtn[j][i] = sTmp;
        }
      }
    } catch (SQLException se) {
      LOG.error(se);
      throw se;
    } catch (NamingException e) {
      LOG.error(e);
      throw new SQLException(e.getMessage());
    } catch (Exception e) {
      LOG.error("",e);
      throw new SQLException(e.getMessage());
    } finally {
      try {
        rsmd = null;
        if (rs != null) {
          rs.close();
          rs = null;
        }
        if (st != null) {
          st.close();
          st = null;
        }
        if (con != null) {
          con.close();
          con = null;
        }
      } catch (SQLException se) {
        se.printStackTrace();
        throw se;
      }
    }
    return sArrRtn;
  }
  
  
  public static String[][] doSQLQuery(String datasource,String sql) throws SQLException {
    String sTmp="";
    String[][] sArrRtn = null;
    int iCol = 0, i = 0, j = 0;
    boolean bFlg = false;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
      JDBCWrapper jw = new JDBCWrapper(datasource);
      con = jw.getConnection();
      st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      rs = st.executeQuery(sql);
      rsmd = rs.getMetaData();
      iCol = rsmd.getColumnCount();
      bFlg = rs.last();
      if (bFlg)
        i = rs.getRow();
      rs.beforeFirst();
      sArrRtn = new String[i + 1][iCol];
      for (i = 0; i < iCol; i++) {
        sTmp = rsmd.getColumnName(i + 1).trim();
        sArrRtn[0][i] = sTmp;
      }
      while (rs.next()) {
        j = j + 1;
        for (i = 0; i < iCol; i++) {
        	 Object obj = rs.getObject(i + 1);
             if(obj!=null&&obj.getClass().getName().equalsIgnoreCase(oracle.sql.CLOB.class.getName())){
             	Clob clob =(Clob)obj;
                 if (clob != null) {
                 	sTmp = clob.getSubString(1, (int) clob.length());                          
                 }
             }
             else{
             	sTmp = String.valueOf(
             	obj == null ? "" : obj).trim();
                 if (sTmp.equals("null"))
                   sTmp = "";
             }                        
             sArrRtn[j][i] = sTmp;
        }
      }
    } catch (SQLException se) {
      LOG.error(se);
      throw se;
    } catch (NamingException e) {
      LOG.error(e);
      throw new SQLException(e.getMessage());
    } catch (Exception e) {
      LOG.error("",e);
      throw new SQLException(e.getMessage());
    } finally {
      try {
        rsmd = null;
        if (rs != null) {
          rs.close();
          rs = null;
        }
        if (st != null) {
          st.close();
          st = null;
        }
        if (con != null) {
          con.close();
          con = null;
        }
      } catch (SQLException se) {
        se.printStackTrace();
        throw se;
      }
    }
    return sArrRtn;
  }
  
  
  public static String[][] doClobSQLQuery(String sql) throws SQLException {
    String sTmp="";
    String[][] sArrRtn = null;
    int iCol = 0, i = 0, j = 0;
    boolean bFlg = false;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
      JDBCWrapper jw = new JDBCWrapper();
      con = jw.getConnection();
      st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      rs = st.executeQuery(sql);
      rsmd = rs.getMetaData();
      iCol = rsmd.getColumnCount();
      bFlg = rs.last();
      if (bFlg)
        i = rs.getRow();
      rs.beforeFirst();
      sArrRtn = new String[i + 1][iCol];
      for (i = 0; i < iCol; i++) {
        sTmp = rsmd.getColumnName(i + 1).trim();
        sArrRtn[0][i] = sTmp;
      }
      while (rs.next()) {
        j = j + 1;
        for (i = 0; i < iCol; i++) {          
        	 Object obj = rs.getObject(i + 1);
             if(obj!=null&&obj.getClass().getName().equalsIgnoreCase(oracle.sql.CLOB.class.getName())){
             	Clob clob =(Clob)obj;
                 if (clob != null) {
                 	sTmp = clob.getSubString(1, (int) clob.length());                          
                 }
             }
             else{
             	sTmp = String.valueOf(
             	obj == null ? "" : obj).trim();
                 if (sTmp.equals("null"))
                   sTmp = "";
             }                        
             sArrRtn[j][i] = sTmp;
          
        }
      }
    } catch (SQLException se) {
      LOG.error("sql="+sql,se);
      throw se;
    } catch (NamingException e) {
      LOG.error("sql="+sql,e);
      throw new SQLException(e.getMessage());
    } catch (Exception e) {
      LOG.error("sql="+sql,e);
      throw new SQLException(e.getMessage());
    } finally {
      try {
        rsmd = null;
        if (rs != null) {
          rs.close();
          rs = null;
        }
        if (st != null) {
          st.close();
          st = null;
        }
        if (con != null) {
          con.close();
          con = null;
        }
      } catch (SQLException se) {
        se.printStackTrace();
        throw se;
      }
    }
    return sArrRtn;
  }

  
  public static String[][] doSQLQuery(String sql, Object[] args)
      throws SQLException {
    String sTmp="";
    String[][] sArrRtn = null;
    int iCol = 0, i = 0, j = 0;
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    boolean bFlg = false;
    try {
      JDBCWrapper jw = new JDBCWrapper();
      con = jw.getConnection();
      pst = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      if (args != null) {
        for (int k = 0; k < args.length; k++) {
          pst.setObject(k + 1, args[k]);
        }
      }
      rs = pst.executeQuery();
      rsmd = rs.getMetaData();
      iCol = rsmd.getColumnCount();
      bFlg = rs.last();
      if (bFlg)
        i = rs.getRow();
      rs.beforeFirst();
      sArrRtn = new String[i + 1][iCol];
      for (i = 0; i < iCol; i++) {
        sTmp = rsmd.getColumnName(i + 1).trim();
        sArrRtn[0][i] = sTmp;
      }
      while (rs.next()) {
        j = j + 1;
        for (i = 0; i < iCol; i++) {
        	 Object obj = rs.getObject(i + 1);
             if(obj!=null&&obj.getClass().getName().equalsIgnoreCase(oracle.sql.CLOB.class.getName())){
             	Clob clob =(Clob)obj;
                 if (clob != null) {
                 	sTmp = clob.getSubString(1, (int) clob.length());                          
                 }
             }
             else{
             	sTmp = String.valueOf(
             	obj == null ? "" : obj).trim();
                 if (sTmp.equals("null"))
                   sTmp = "";
             }                        
             sArrRtn[j][i] = sTmp;
        }
      }
    } catch (SQLException se) {
      LOG.error(se);
      throw se;
    } catch (NamingException e) {
      LOG.error(e);
      throw new SQLException(e.getMessage());
    } catch (Exception e) {
      LOG.error(e);
      throw new SQLException(e.getMessage());
    } finally {
      try {
        rsmd = null;
        if (rs != null) {
          rs.close();
          rs = null;
        }
        if (pst != null) {
          pst.close();
          pst = null;
        }
        if (con != null) {
          con.close();
          con = null;
        }
      } catch (SQLException se) {
        se.printStackTrace();
        throw se;
      }
    }
    return sArrRtn;
  }

  
  public static boolean doSQLUpdate(String sql) throws SQLException {
    boolean flag = false;
    JDBCWrapper jw = null;
    try {
      jw = new JDBCWrapper();
      jw.setAutoCommit(false);
      jw.createStatement();

      int a = jw.executeUpdate(sql);
      flag = a > 0 ? true : false;
      jw.commit();
    } catch (Exception e) {
      LOG.error("",e);
      try {
        jw.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
        throw e1;
      }
      throw new SQLException(e.getMessage());
    } finally {
      try {
        jw.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
        throw e1;
      }
    }
    return flag;
  }


  public static boolean doSQLUpdate(String sql, Object[] args)
      throws SQLException {
    boolean flag = false;
    JDBCWrapper jw = null;
    try {
      jw = new JDBCWrapper();
      jw.setAutoCommit(false);
      jw.prepareStatement(sql);
      if (args != null) {
        for (int i = 0; i < args.length; i++) {
          jw.setObject(i + 1, args[i]);
        }
      }
      int a = jw.executeUpdate();
      flag = a > 0 ? true : false;
      jw.commit();
    } catch (Exception e) {
      LOG.error("",e);
      try {
        jw.rollback();
      } catch (SQLException e1) {
        e1.printStackTrace();
        throw e1;
      }
      throw new SQLException(e.getMessage());
    } finally {
      try {
        jw.close();
      } catch (SQLException e1) {
        e1.printStackTrace();
        throw e1;
      }
    }
    return flag;
  }
}